﻿function Drop-RedundantSpatialWareObjects {
<#
.Synopsis
  Insert new Enrolment Addresses into the CCD Lookup SQL Server table.
.Parameter ServerName
  The name of the SQL Server instance.
.Parameter DatabaseName
#>
[CmdletBinding()]
param(
    [Parameter(Position=1, Mandatory=$true, ValueFromPipelineByPropertyName=$true)]
    [string]$ServerName
    ,
    [Parameter(Position=2, Mandatory=$true, ValueFromPipelineByPropertyName=$true)]
    [string]$DatabaseName
)
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') >> $null
    $server = new-object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName
    $db = $server.Databases[$DatabaseName]
    
    $udfNames = new-object object[] $db.UserDefinedFunctions.Count
    
    for($i = 0; $i -lt $db.UserDefinedFunctions.Count; $i++)
    {
        $udfNames[$i] = $db.UserDefinedFunctions[$i].Name
    }
    
    foreach ($udfName in $udfNames)
    {
        [string[]]$parts = [string[]]$udfName.Split("#")
        if ($parts.Count -eq 5)
        {            
            $table = $db.Tables[$parts[0]]
            if ($table)
            {
                write-host "$table table exists for $udfName" -ForegroundColor Green
            }
            else
            {
                $udf = $db.UserDefinedFunctions[$udfName]
                write-warning "Dropping $udf as it does not have a corresponding table $table ..."
                $udf.Drop()
                write-host "$udf successfully dropped."
            }
        }
        else
        {
            write-host "$udfName is NOT a SpatialWare UDF."
        }
    }
}